"""
CREATE TABLE `virtAnalysis` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  `cmd` varchar(100) NOT NULL COMMENT 'name',
  `cmdType` varchar(16) NOT NULL COMMENT 'name',
  `expType` varchar(16) NOT NULL COMMENT 'name',
  `result` varchar(16) NOT NULL DEFAULT '',
  `reqChatTime` varchar(20) NOT NULL COMMENT 'name',
  `respCtrlScreenTime` varchar(20) NOT NULL COMMENT 'name',
  `reqContentTime` varchar(20) NOT NULL COMMENT 'name',
  `reqContentJson` varchar(20) NOT NULL COMMENT 'name',
  `respAlalysisTime` varchar(20) NOT NULL COMMENT 'name',
  `execSqlTime` varchar(20) NOT NULL COMMENT 'name',
  `sqlToJsonTime` varchar(20) NOT NULL COMMENT 'name',
  `reqWenxinChat` varchar(20) NOT NULL COMMENT 'name',
  `respWenxinChat` varchar(20) NOT NULL COMMENT 'name',
  `endTime` varchar(20) NOT NULL COMMENT 'name',
  `totalCost` float(5,1) NOT NULL DEFAULT '0' COMMENT 'ZongHaoshi',
  `intentCost` float(5,1) NOT NULL DEFAULT '0' COMMENT 'YituHaoshi',
  `genSqlCost` float(5,1) NOT NULL DEFAULT '0' COMMENT 'createSql',
  `execSqlCost` float(5,1) NOT NULL DEFAULT '0' COMMENT 'executeSql',
  `genChatCost` float(5,1) NOT NULL DEFAULT '0' COMMENT 'genChat',
  `content` varchar(1200) NOT NULL DEFAULT '',
  `create_time` varchar(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2512391 DEFAULT CHARSET=utf8 COMMENT='operation times';

"""

"""
chat sql
"""

import xlrd
import sys
import os
import pymysql

"""
| id                 | bigint(64)   | NO   | PRI | NULL     | auto_increment |
| cmd                | varchar(100) | NO   |     | NULL     |                |
| cmdType            | varchar(16)  | NO   |     | NULL     |                |
| chatVal            | varchar(32)  | NO   |     | NULL     |                |
| expType            | varchar(16)  | NO   |     | NULL     |                |
| expChatVal         | varchar(32)  | NO   |     | NULL     |                |
| reqChatTime        | varchar(20)  | NO   |     | NULL     |                |
| respChatTime       | varchar(20)  | NO   |     | NULL     |                |
| respCtrlScreenTime | varchar(20)  | NO   |     | NULL     |                |
| respEndStartTime   | varchar(20)  | NO   |     | NULL     |                |
| totalCost          | float(5,1)   | NO   |     | 0.0      |                |
| intentCost         | float(5,1)   | NO   |     | 0.0      |                |
| ansCost            | float(5,1)   | NO   |     | 0.0      |                |
| result             | varchar(16)  | NO   |     |          |                |
| myCost             | float(10,6)  | NO   |     | 0.000000 |                |
| content            | varchar(350) | NO   |     |          |                |
| create_time        | varchar(20)  | NO   |     | 0
"""
def conMysql():
    conn = pymysql.connect(
        host = '10.12.72.138',
        port = 8306,
        user = 'tfx',
        passwd = 'tfxg0',
        database = 'overcloud',
        charset = 'utf8'
    )

    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    """
    cursor=pymysql.cursors.DictCursor
    将查询结果以字典的形式返回
    """
    return cursor,conn
    # res = cursor.execute('select * from virtControl where result="FAIL"')


def is_float(str):
    try:
        float(str)
        return True
    except ValueError:
        return False

def genSql(fname, ts,cursor,conn):
    """ gen sql """
    workbook = xlrd.open_workbook(r'{}'.format(fname))
    sheet = workbook.sheet_by_name('讲解')
    for i in range(1, sheet.nrows):
        row = sheet.row_values(i)
        sql = 'insert into virtControl values(NULL,'
        for j in range(len(row) - 1):
            if j == 11 or j == 13:
                # if float(str(row[j])) or str(row[j]).startswith('0.'):
                if is_float(row[j]):
                    sql += "{},".format(row[j])
                else:
                    sql += "9999,"
                # print(type(str(row[j])),str(row[j]))
                continue
            else:
                sql += "'{}',".format(row[j])
                continue
            # if j == 14:
            #     sql += "'{}',".format(row[j].replace("'", "\\\'").replace("\n", "\\n"))
        sql += "'{}');".format(ts)
        print(sql)
        cursor.execute(sql)
    conn.commit()


if __name__ == '__main__':

    # genSql(sys.argv[1], sys.argv[2])
    cursor, conn = conMysql()
    genSql('/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_控屏类指令_2023-10-17_17_26_10.xls',
           '2023-10-17 17:26:10',cursor,conn)
